package com.cnsugar.common.jdbc;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.cnsugar.common.context.AppContext;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.util.Assert;

/**
 * 根据数据库表生成实体类
 *
 * @Author Sugar
 * @Version 2018/12/7 13:59
 */
public class GenerateClass {

    /**
     * 返回JdbcTemplate对象
     *
     * @return
     */
    private static JdbcTemplate getJdbcTemplate() {
        return (JdbcTemplate) AppContext.getBean("jdbcTemplate");
    }

    /**
     * @param table       表名
     * @param jdbcSupport 是否添加@Field注解
     * @return
     */
    public static String getMysqlProperties(String table, boolean jdbcSupport) {
        return getMysqlProperties(null, table, jdbcSupport);
    }

    /**
     * @param table 表名
     * @return
     */
    public static String getMysqlProperties(String table) {
        return getMysqlProperties(null, table, true);
    }

    /**
     * 获取Oracle表结构信息
     *
     * @param table       表名
     * @param jdbcSupport 是否添加@Field注解
     * @return
     */
    public static String getOracleProperties(String table, boolean jdbcSupport) {
        return getOracleProperties(null, table, jdbcSupport);
    }

    /**
     * 获取Oracle表结构信息
     *
     * @param table 表名
     * @return
     */
    public static String getOracleProperties(String table) {
        return getOracleProperties(null, table, true);
    }

    /**
     * 获取Mysql表结构信息
     *
     * @param database    数据库名
     * @param table       表名
     * @param jdbcSupport 是否添加@Field注解
     * @return
     */
    public static String getMysqlProperties(String database, String table, boolean jdbcSupport) {
        JdbcTemplate jt = getJdbcTemplate();
        if (database == null) {
            try {
                database = jt.getDataSource().getConnection().getCatalog();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        String sql = "SELECT COLUMN_NAME,DATA_TYPE,COLUMN_DEFAULT,COLUMN_COMMENT,COLUMN_TYPE,COLUMN_KEY FROM information_schema.`COLUMNS` WHERE TABLE_SCHEMA='"
                + database + "' AND TABLE_NAME='" + table + "'";
        List list = queryForList(sql, jt);
        if (list == null || list.size() == 0) {
            return database + "数据库不存在，或" + table + "表不存在";
        }
        StringBuilder str = new StringBuilder();
        if (jdbcSupport) {
            str.append("\n@Table(\"" + table + "\")");
        }
        str.append("\npublic class ");
        String[] words = table.toLowerCase().split("_");
        for (String word : words) {
            str.append((word.charAt(0) + "").toUpperCase()).append(word.substring(1));
        }
        str.append(" implements Serializable {");
        StringBuilder column = new StringBuilder();
        StringBuilder method = new StringBuilder();
        for (int i = 0; i < list.size(); i++) {
            Map m = (Map) list.get(i);
            String javaType = null;
            String type = m.get("DATA_TYPE").toString().toLowerCase();// 数据类型
            boolean b = false, s = false;
            boolean isPk = "PRI".equals(m.get("COLUMN_KEY") + "");
            if (type.startsWith("bigint")) {
                javaType = "Long";
                b = true;
            } else if (type.startsWith("int") || type.startsWith("tinyint")) {
                javaType = "Integer";
                b = true;
            } else if (type.startsWith("float")) {
                javaType = "Float";
                b = true;
            } else if (type.startsWith("double") || type.startsWith("decimal")) {
                javaType = "Double";
                b = true;
            } else if (type.startsWith("date")) {
                javaType = "Date";
            } else if (type.startsWith("timestamp")) {
                javaType = "Timestamp";
            } else if (type.startsWith("smallint") || type.equals("char(1)")) {
                javaType = "Short";
                b = true;
            } else if (type.startsWith("blob")) {
                javaType = "byte[]";
            } else {
                javaType = "String";
                s = true;
            }
            String col = m.get("COLUMN_NAME").toString();
            String fieldName = null;
            if (jdbcSupport) {
                String[] name = col.toLowerCase().split("_");
                fieldName = name[0];
                for (int j = 1; j < name.length; j++) {
                    fieldName += (name[j].charAt(0) + "").toUpperCase() + name[j].substring(1);
                }
            } else {
                fieldName = col.toLowerCase();// 字段名
            }
            if (jdbcSupport) {
                if (isPk) {
                    str.append("\n\n\t@Field(value=Column." + col.toUpperCase() + ", isPK=true)");
                } else {
                    str.append("\n\n\t@Field(Column." + col.toUpperCase() + ")");
                }
            } else {
                str.append("\n");
            }
            String comment = (String) m.get("COLUMN_COMMENT");
            //可以扩展，增加自己的注解
//            if (export) {
//                str.append("\n\t@ExcelField(title=\"" + (comment == null ? fieldName : comment) + "\")");
//            }
            str.append("\n\tprivate ").append(javaType).append(" ").append(fieldName);
            String def = (String) m.get("COLUMN_DEFAULT");// 默认值
            if (!(def == null || def.isEmpty() || def.equals("NULL"))) {
                if (b) {
                    str.append(" = ").append(def);
                } else if (s) {
                    str.append(" = \"").append(def).append("\"");
                }
            }
            str.append(";");
            if (comment != null) {
                str.append(" //").append(comment);
            }
            column.append("\n\t\tpublic static final String ").append(col.toUpperCase()).append(" = \"").append(col).append("\";");
            //生成setter方法
            method.append("\n\tpublic void set").append((fieldName.charAt(0) + "").toUpperCase()).append(fieldName.substring(1)).append("(").append(javaType)
                    .append(" ").append(fieldName).append(") {");
            method.append("\n\t\tthis.").append(fieldName).append(" = ").append(fieldName).append(";");
            method.append("\n\t}");
            //生成getter方法
            method.append("\n\tpublic ").append(javaType).append(" get").append((fieldName.charAt(0) + "").toUpperCase()).append(fieldName.substring(1))
                    .append("() {");
            method.append("\n\t\treturn this.").append(fieldName).append(";");
            method.append("\n\t}");
        }
        str.append("\n\n").append(method);
        str.append("\n\n\tpublic static class Column {").append(column).append("\n\t}");
        str.append("\n}\n");
        return str.toString();
    }

    /**
     * 获取Oracle表结构信息
     *
     * @param owner       用户名
     * @param table       表名
     * @param jdbcSupport 是否添加@Field注解
     * @return
     */
    public static String getOracleProperties(String owner, String table, boolean jdbcSupport) {
        JdbcTemplate jt = getJdbcTemplate();
        if (owner == null) {
            try {
                owner = jt.getDataSource().getConnection().getMetaData().getUserName();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        String sql = "SELECT A.COLUMN_NAME,A.DATA_TYPE,A.DATA_LENGTH,A.DATA_PRECISION,A.DATA_SCALE,A.DATA_DEFAULT, A.NULLABLE,B.COMMENTS,"
                + "(SELECT 1 FROM USER_CONS_COLUMNS C, USER_CONSTRAINTS D WHERE C.COLUMN_NAME=A.COLUMN_NAME AND C.TABLE_NAME=A.TABLE_NAME"
                + " AND D.CONSTRAINT_NAME=C.CONSTRAINT_NAME AND D.TABLE_NAME=C.TABLE_NAME AND D.CONSTRAINT_TYPE='P') AS PK"
                + " FROM USER_TAB_COLUMNS A LEFT JOIN USER_COL_COMMENTS B"
                + " ON (B.COLUMN_NAME=A.COLUMN_NAME AND B.TABLE_NAME=A.TABLE_NAME)"
                + " WHERE A.TABLE_NAME=UPPER('" + table + "') ORDER BY A.COLUMN_ID";
        List list = queryForList(sql, jt);
        if (list == null || list.size() == 0) {
            return "表空间" + owner + "中不存在表：" + table;
        }
        StringBuilder str = new StringBuilder();
        if (jdbcSupport) {
            str.append("\n@Table(\"" + table.toUpperCase() + "\")");
        }
        str.append("\npublic class ");
        String[] words = table.toLowerCase().split("_");
        for (String word : words) {
            str.append((word.charAt(0) + "").toUpperCase()).append(word.substring(1));
        }
        str.append(" implements Serializable {");
        StringBuilder column = new StringBuilder();
        StringBuilder method = new StringBuilder();
        for (int i = 0; i < list.size(); i++) {
            Map m = (Map) list.get(i);
            String javaType = null;
            String type = m.get("DATA_TYPE").toString().toLowerCase();// 数据类型
            boolean b = false, s = false;
            boolean isPk = "1".equals(m.get("PK") + "");
            if (type.startsWith("number")) {
                int len = parseInt(m.get("DATA_PRECISION"));
                int sc = parseInt(m.get("DATA_SCALE"));
                if (sc == 0) {
                    if (len > 0 && len <= 4) {
                        javaType = "Short";
                    } else if (len > 0 && len <= 9) {
                        javaType = "Integer";
                    } else {
                        javaType = "Long";
                    }
                } else {
                    javaType = "Double";
                }
                b = true;
            } else if (type.startsWith("bigint")) {
                javaType = "Long";
                b = true;
            } else if (type.startsWith("int") || type.startsWith("tinyint")) {
                javaType = "Integer";
                b = true;
            } else if (type.startsWith("float")) {
                javaType = "Float";
                b = true;
            } else if (type.startsWith("double") || type.startsWith("decimal")) {
                javaType = "Double";
                b = true;
            } else if (type.startsWith("date")) {
                javaType = "Date";
            } else if (type.startsWith("timestamp")) {
                javaType = "Timestamp";
            } else if (type.startsWith("smallint") || type.equals("char(1)") || type.equals("nchar")) {
                javaType = "Short";
                b = true;
            } else if (type.startsWith("blob")) {
                javaType = "byte[]";
            } else {
                javaType = "String";
                s = true;
            }
            String col = m.get("COLUMN_NAME").toString();
            String fieldName = null;
            if (jdbcSupport) {
                String[] name = col.toLowerCase().split("_");
                fieldName = name[0];
                for (int j = 1; j < name.length; j++) {
                    fieldName += (name[j].charAt(0) + "").toUpperCase() + name[j].substring(1);
                }
            } else {
                fieldName = col.toLowerCase();// 字段名
            }
            if (jdbcSupport) {
                if (isPk) {
                    str.append("\n\n\t@Field(value=Column." + col.toUpperCase() + ", isPK=true)");
                } else {
                    str.append("\n\n\t@Field(Column." + col.toUpperCase() + ")");
                }
            } else {
                str.append("\n");
            }
            String comment = (String) m.get("COMMENTS");
            //可以扩展，增加自己的注解
//            if (export) {
//                str.append("\n\t@ExcelField(title=\"" + (comment == null ? fieldName : comment) + "\")");
//            }
            str.append("\n\tprivate ").append(javaType).append(" ").append(fieldName);
            String def = (String) m.get("DATA_DEFAULT");// 默认值
            if (!(def == null || def.isEmpty() || def.equals("NULL"))) {
                if (b) {
                    str.append(" = ").append(def);
                } else if (s) {
                    str.append(" = \"").append(def).append("\"");
                }
            }
            str.append(";");
            if (comment != null) {
                str.append(" //").append(comment);
            }
            column.append("\n\t\tpublic static final String ").append(col.toUpperCase()).append(" = \"").append(col).append("\";");
            //生成setter方法
            method.append("\n\tpublic void set").append((fieldName.charAt(0) + "").toUpperCase()).append(fieldName.substring(1)).append("(").append(javaType)
                    .append(" ").append(fieldName).append(") {");
            method.append("\n\t\tthis.").append(fieldName).append(" = ").append(fieldName).append(";");
            method.append("\n\t}");
            //生成getter方法
            method.append("\n\tpublic ").append(javaType).append(" get").append((fieldName.charAt(0) + "").toUpperCase()).append(fieldName.substring(1))
                    .append("() {");
            method.append("\n\t\treturn this.").append(fieldName).append(";");
            method.append("\n\t}");
        }
        str.append("\n\n").append(method);
        str.append("\n\n\tpublic static class Column {").append(column).append("\n\t}");
        str.append("\n}\n");
        return str.toString();
    }

    /**
     * 返回一个List<Map>
     *
     * @param sql
     * @param jt
     * @return
     */
    private static List<Map<String, Object>> queryForList(String sql,
                                                          JdbcTemplate jt) {
        Assert.notNull(sql);
        Assert.notNull(jt, "JdbcTemplate not be null");
        try {
            final List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
            RowCallbackHandler hander = new RowCallbackHandler() {
                public void processRow(ResultSet rs) throws SQLException {
                    Map<String, Object> m = new HashMap<String, Object>();
                    ResultSetMetaData meta = rs.getMetaData();
                    if (meta != null) {
                        int colCount = meta.getColumnCount();
                        for (int i = 1; i <= colCount; i++) {
                            String fieldName = meta.getColumnName(i);
                            Object fieldValue = rs.getObject(fieldName);

                            m.put(fieldName, fieldValue);
                        }
                    }
                    list.add(m);
                }
            };
            jt.query(sql, hander);

            return list;
        } catch (DataAccessException e) {
            e.printStackTrace();
            return null;
        }
    }

    private static int parseInt(Object obj) {
        if (obj == null) {
            return 0;
        }
        try {
            return Integer.parseInt(obj.toString());
        } catch (NumberFormatException e) {
            return 0;
        }
    }
}
